Index use for case insensitive query
От | eric soroos |
---|---|
Тема | Index use for case insensitive query |
Дата | |
Msg-id | 46780784.1178649587@[4.42.179.151] обсуждение исходный текст |
Ответы |
Re: Index use for case insensitive query
|
Список | pgsql-novice |
I've got a table where one of the main lookup access is by email address, which is generally a case insensitive lookup. With the constraints that: some of the entries aren't reall email addresses and I can't change the case of all the data becauseof that: Is there a way to do a case insensitive index and appropriate query so that when searching for an email address, I get thebenefit of an index? In the following queries, there's an index on dl_profile(_email). test=# explain analyze select _donorNum from dl_profile where _email~'^foo@bar.org$' ; NOTICE: QUERY PLAN: Index Scan using dl_profile_email on dl_profile (cost=0.00..467.75 rows=1 width=4) (actual time=14.59..14.63 rows=1 loops=1) Total runtime: 14.97 msec EXPLAIN test=# explain analyze select _donorNum from dl_profile where _email~*'^foo@bar.org$' ; NOTICE: QUERY PLAN: Seq Scan on dl_profile (cost=0.00..10607.28 rows=1 width=4) (actual time=4196.43..5078.86 rows=1 loops=1) Total runtime: 5079.42 msec thanks eric
В списке pgsql-novice по дате отправления: